﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Pantry.Commons;

namespace Pantry.DataAccess
{
    public class CountyDao
    {
        private SqlConnection _sqlConnection;
        #region Sql Command

        private const string SqlGetAllCounty =
            @"SELECT County.CountyID,CountyName,PostCode,TownName,County.CountryId 
                FROM County,Town
                WHERE County.CountyId = Town.CountyId
                ORDER BY CountyName ASC";

        private const string SqlCheckPostcodeValid = @"
                    SELECT COUNT(Postcode) AS PostcodeCount
                    FROM    County AS A
                    INNER JOIN Town AS B ON A.CountyId = B.CountyId
                    WHERE A.Postcode = @Postcode 
                    GROUP BY Postcode
                    ORDER BY Postcode ASC
                    ";
        #endregion

        #region Method

        public DataTable SelectAllCounty()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlGetAllCounty);
            return (ds.Tables[0] ?? null);
        }

        public int CheckPostcodeValid(string postcode)
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
            var parameter = new SqlParameter("@Postcode", SqlDbType.NVarChar) {Value = postcode};
            var dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlCheckPostcodeValid, parameter);
            return dataSet.Tables[0] != null ? (int) dataSet.Tables[0].Rows[0]["PostcodeCount"] : 0;
        }
        #endregion
    }
}
